#!/usr/bin/perl

use DBI;

$SRC_DB=$ENV{'SRC_DB'};
$TGT_DB=$ENV{'TGT_DB'};
sub s_table_cnt
{
	my($table_name, $rule_name, $layout_id) = @_;

#print "--- s_tale_cnt(), table_name:$table_name\n";

	if( $table_name eq 'META_TRANSFORM_EXPRESSION')  {
		$sub_sql01 = "
	TRANSFORM_RULE_ID 	= '$rule_name'
"
	} else {
		$sub_sql01 = "
	TRANSFORM_RULE_ID 	= '$rule_name'
AND	LAYOUT_ID 		= '$layout_id'
"
	}

	$sql = "
SELECT 
COUNT(*)
FROM 	$table_name A
WHERE 
$sub_sql01
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	@data = $sth -> fetchrow_array;

	$sth->finish;
	$s_table_cnt = $data[0];
#print "--- s_tale_cnt(), s_table_cnt:$s_table_cnt\n";
}

#----------------------------------------------------------------
sub s_DEFERRED_TRAN_TRANSFORM_RULE_cnt
{
	my($src_id, $tgt_id, $rule_name) = @_;

print "--- s_tale_cnt(), table_name:$src_id, $tgt_id, $rule_name\n";

	$sql = "
SELECT 
COUNT(*)
FROM 	DEFERRED_TRAN_TRANSFORM_RULE
WHERE 
	SOURCE_INTERFACE_ID	= '$src_id'
AND	TARGET_INTERFACE_ID	= '$tgt_id'
AND	TRANSFORM_RULE_ID	= '$rule_name'
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	@data = $sth -> fetchrow_array;

	$sth->finish;
	$s_table_cnt = $data[0];
#print "--- s_DEFERRED_TRAN_TRANSFORM_RULE(), s_table_cnt:$s_table_cnt\n";
}


#----------------------------------------------------------------
sub s_ONLINE_TRAN_cnt
{
	local(@i_para) = @_;

#print "--- s_ONLINE_TRAN(): $i_para[0], $i_para[1], $i_para[2],";
#print "$i_para[3], $i_para[4], $i_para[5]\n";

	$sql = "
SELECT 
COUNT(*)
FROM 	ONLINE_TRAN
WHERE 
	MESSAGE_TYPE	= ?
AND	ONLINE_TRANSACTION_CODE	= ?
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para);
  
	@data = $sth -> fetchrow_array;

	$sth->finish;
	$s_ONLINE_TRAN_cnt = $data[0];
#print "--- s_ONLINE_TRAN(), s_table_cnt:$s_table_cnt\n";
}

#----------------------------------------------------------------
sub s_META_TRANSFORM_EXPRESSION_cnt
{
	local(
		$l_TRANSFORM_RULE_ID,
		$l_TRANSFORM_SEQUENCE
	) = @_;

#print "--- s_ONLINE_TRAN(): $i_para[0], $i_para[1], $i_para[2],";
#print "$i_para[3], $i_para[4], $i_para[5]\n";

	$sql = "
SELECT 
COUNT(*)
FROM 	META_TRANSFORM_EXPRESSION
WHERE
	TRANSFORM_RULE_ID	= '$l_TRANSFORM_RULE_ID'
AND	TRANSFORM_SEQUENCE	= '$l_TRANSFORM_SEQUENCE'
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	@data = $sth -> fetchrow_array;

	$sth->finish;
	$s_ONLINE_TRAN_cnt = $data[0];
#print "--- s_ONLINE_TRAN(), s_table_cnt:$s_table_cnt\n";
}

#----------------------------------------------------------------
sub s_META_TRANSFORM_LAYOUT_cnt
{
	local(
		$l_TRANSFORM_RULE_ID,
		$l_LAYOUT_ID
	) = @_;

#print "--- s_ONLINE_TRAN(): $i_para[0], $i_para[1], $i_para[2],";
#print "$i_para[3], $i_para[4], $i_para[5]\n";

	$sql = "
SELECT 
COUNT(*)
FROM 	META_TRANSFORM_LAYOUT
WHERE 
	TRANSFORM_RULE_ID	= $l_TRANSFORM_RULE_ID,
AND	LAYOUT_ID		= $l_LAYOUT_ID
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	@data = $sth -> fetchrow_array;

	$sth->finish;
	$s_ONLINE_TRAN_cnt = $data[0];
#print "--- s_ONLINE_TRAN(), s_table_cnt:$s_table_cnt\n";
}
#----------------------------------------------------------------
sub s_is_in_or_out_tran
{
	my($app_name, $rule_name) = @_;

#print "--- s_is_in_or_out_tran(), app_name:$app_name, $rule_name\n";

#-----------------------------
	$sql = "
SELECT 
COUNT(*)
FROM 	NNR_APP A,
	NNR_MSG_MSTR B,
	NNR_RULE C,
	NNR_ARG D,
	NNR_OPERATION E,
	NNR_OPERATOR F
--	NNR_BOOLEAN G
WHERE 		
	A.APP_NAME = '$app_name'
AND	C.RULE_NAME = '$rule_name'
AND E.FLD_NAME1 = 'STR0015'
AND	B.MSG_ID = C.MSG_ID
AND 	A.APP_ID = C.APP_ID
AND 	D.APP_ID = C.APP_ID
AND	D.MSG_ID = C.MSG_ID
AND	D.RULE_ID = C.RULE_ID
AND 	E.APP_ID = C.APP_ID
AND	E.MSG_ID = C.MSG_ID
AND	E.OPERN_ID = D.OPERN_ID
AND	F.OPER_ID = E.OPER_ID
";
  	$sth = $db2->prepare($sql);
	$sth->execute();
	@data = $sth -> fetchrow_array;
	$sth->finish;

	$ret1 = $data[0];
#print "--- s_is_in_or_out_tran(), s_table_cnt:$ret1\n";
#-----------------------------

	$sql = "
SELECT 
COUNT(*)
FROM 	NNR_APP A,
	NNR_MSG_MSTR B,
	NNR_RULE C,
	NNR_ARG D,
	NNR_OPERATION E,
	NNR_OPERATOR F
--	NNR_BOOLEAN G
WHERE 		
	A.APP_NAME = '$app_name'
AND	C.RULE_NAME = '$rule_name'
AND 	( (E.FLD_NAME1 = 'STR0001') OR
	(E.FLD_NAME1 = 'STR0016_1') OR
	(E.FLD_NAME1 = 'STR0016_2') )
AND	B.MSG_ID = C.MSG_ID
AND 	A.APP_ID = C.APP_ID
AND 	D.APP_ID = C.APP_ID
AND	D.MSG_ID = C.MSG_ID
AND	D.RULE_ID = C.RULE_ID
AND 	E.APP_ID = C.APP_ID
AND	E.MSG_ID = C.MSG_ID
AND	E.OPERN_ID = D.OPERN_ID
AND	F.OPER_ID = E.OPER_ID
";
  	$sth = $db2->prepare($sql);
	$sth->execute();
	@data = $sth -> fetchrow_array;
	$sth->finish;

	$ret2 = $data[0];
#print "--- s_is_in_or_out_tran(), s_table_cnt:$ret2\n";
#-----------------------------


	printf "------> s_is_in_or_out_tran : $ret1, $ret2 \n";
	if( $ret1 >= 1 ) {
		$s_is_in_or_out_tran = 'L';
	} elsif( $ret2 >= 1 ) {
		$s_is_in_or_out_tran = 'I';
# 2009.11.09 
	} else {
		$s_is_in_or_out_tran = 'E';
	}
}
#----------------------------------------------------------------

sub s_online_tran_code
{
	my($app_name, $rule_name) = @_;

#print "--- s_is_in_or_out_tran(), app_name:$app_name, $rule_name\n";

#-----------------------------
	$sql = "
SELECT 
	D.ARG_VAL
FROM 	NNR_APP A,
	NNR_MSG_MSTR B,
	NNR_RULE C,
	NNR_ARG D,
	NNR_OPERATION E,
	NNR_OPERATOR F
--	NNR_BOOLEAN G
WHERE 		
	A.APP_NAME = '$app_name'
AND	C.RULE_NAME = '$rule_name'
AND 	( (E.FLD_NAME1 = 'STR0015') OR
	(E.FLD_NAME1 = 'STR0001') )
AND	B.MSG_ID = C.MSG_ID
AND 	A.APP_ID = C.APP_ID
AND 	D.APP_ID = C.APP_ID
AND	D.MSG_ID = C.MSG_ID
AND	D.RULE_ID = C.RULE_ID
AND 	E.APP_ID = C.APP_ID
AND	E.MSG_ID = C.MSG_ID
AND	E.OPERN_ID = D.OPERN_ID
AND	F.OPER_ID = E.OPER_ID
";
  	$sth = $db2->prepare($sql);
	$sth->execute();
	@data = $sth -> fetchrow_array;
	$sth->finish;

	$ret2 = $data[0];
#print "--- s_is_in_or_out_tran(), s_table_cnt:$ret2\n";
#-----------------------------


	$s_online_tran_code = $ret2;
}
#----------------------------------------------------------------
sub d_table
{
	my($table_name, $rule_name, $layout_id) = @_;

	if( ($table_name eq 'META_TRANSFORM_EXPRESSION') ||
		($table_name eq 'META_TRANSFORM_LAYOUT') ){
		$sub_sql01 = "
	TRANSFORM_RULE_ID 	= '$rule_name'
"
	} else {
		$sub_sql01 = "
	TRANSFORM_RULE_ID 	= '$rule_name'
AND	LAYOUT_ID 		= '$layout_id'
"
	}
	$sql = "
DELETE $table_name
WHERE 
$sub_sql01
";

#print "- d_table() : $table_name --$sql\n----\n";

  	$sth = $db->prepare($sql);

	$sth->execute()
		or die "Couldn't execute statement: " . $sth->errstr;

	$sth->finish;
}

#----------------------------------------------------------------

sub d_DEFERRED_TRAN_TRANSFORM_RULE
{
	my($src_id, $tgt_id, $rule_name) = @_;

	$sql = "
DELETE DEFERRED_TRAN_TRANSFORM_RULE
WHERE 
	SOURCE_INTERFACE_ID	= '$src_id'
AND	TARGET_INTERFACE_ID	= '$tgt_id'
AND	TRANSFORM_RULE_ID	= '$rule_name'
";
  	$sth = $db->prepare($sql);

	$sth->execute()
		or die "Couldn't execute statement: " . $sth->errstr;

	$sth->finish;
}

#----------------------------------------------------------------

sub d_ONLINE_TRAN
{
	my($message_type, $online_tean_code) = @_;

	$sql = "
DELETE ONLINE_TRAN
WHERE 
	MESSAGE_TYPE	= '$message_type'
AND	ONLINE_TRANSACTION_CODE	= '$online_tean_code'
";
  	$sth = $db->prepare($sql);

	$sth->execute()
		or die "Couldn't execute statement: " . $sth->errstr;

	$sth->finish;
}


sub d_ONLINE_TRAN_TRANSFORM_RULE
{
	my($message_type, $online_tean_code) = @_;

	$sql = "
DELETE ONLINE_TRAN_TRANSFORM_RULE
WHERE 
	MESSAGE_TYPE	= '$message_type'
AND	ONLINE_TRANSACTION_CODE	= '$online_tean_code'
";
  	$sth = $db->prepare($sql);

	$sth->execute()
		or die "Couldn't execute statement: " . $sth->errstr;

	$sth->finish;
}

sub i_META_TRANSFORM_RULE
{
	local(@i_para_) = @_;
print "--- insert META_TRANSFORM_RULE($i_para_[0], $i_para_[1]\n";
	$sql = "
INSERT INTO META_TRANSFORM_RULE(
TRANSFORM_RULE_ID,
LAYOUT_ID,
DESCRIPTION,
AUTHOR_ID,
OPERATOR_ID
-- LAST_UPDATE_TIME
) VALUES (
?, ?, ?, ?, ?
)
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para_);
  
	$sth->finish;
print "--- insert META_TRANSFORM_RULE..\n";
}

sub i_ONLINE_TRAN_TRANSFORM_RULE
{
	local(@i_para) = @_;
print "--- insert ONLINE_TRAN_TRANSFORM_RULE($i_para[0],$i_para[1],";
print "REQ_RES_YN:$i_para[2],$i_para[3],$i_para[4],$i_para[5])\n";
	$sql = "
INSERT INTO ONLINE_TRAN_TRANSFORM_RULE(
	MESSAGE_TYPE,
	ONLINE_TRANSACTION_CODE,
	REQ_RES_YN,
	COMPARE_SEQUENCE,
	COMPARE_EXPRESSION,
	TRANSFORM_RULE_ID
) VALUES (
?, ?, ?, ?, ?, ?
)
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para);
  
	$sth->finish;
#print "--- insert ONLINE_TRAN_TRANSFORM_RULE..\n";
}

sub u_META_TRANSFORM_RULE
{
	local(@i_para) = @_;
print "--- update META_TRANSFORM_RULE($i_para[0], $i_para[1])\n";
	$l_TRANSFORM_RULE_ID	= $i_para[0];
	$l_LAYOUT_ID		= $i_para[1];
	$l_DESCRIPTION		= $i_para[2];
	$l_AUTHOR_ID		= $i_para[3];
	$l_OPERATOR_ID 		= $i_para[4];
	$sql = "
UPDATE META_TRANSFORM_RULE
SET
	DESCRIPTION	= '$l_DESCRIPTION',
	AUTHOR_ID	= '$l_AUTHOR_ID',
	OPERATOR_ID	= '$l_OPERATOR_ID'
-- LAST_UPDATE_TIME
WHERE
	TRANSFORM_RULE_ID	= '$l_TRANSFORM_RULE_ID'
AND	LAYOUT_ID		= '$l_LAYOUT_ID'
";
#print ">>>>>$sql<<<<\n";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	$sth->finish;
}
#----------------------------------------------------------------
sub u_META_TRANSFORM_EXPRESSION
{
	local(@i_para) = @_;
print "--- update META_TRANSFORM_EXPRESSION($i_para[0], $i_para[1])\n";
#print "       TRANSFORM_TYPE:$i_para[2]<\n";
	$l_TRANSFORM_RULE_ID	= $i_para[0];
	$l_TRANSFORM_SEQUENCE	= $i_para[1];
	$l_TRANSFORM_TYPE	= $i_para[2];
	$l_TARGET_FIELD_NAME	= $i_para[3];
	$l_TRANSFORM_EXPRESSION	= $i_para[4];
	$l_ARRAY_FIELD_NAME	= $i_para[5];

	$sql = "
UPDATE META_TRANSFORM_EXPRESSION
SET
	TRANSFORM_TYPE		= '$l_TRANSFORM_TYPE',
	TARGET_FIELD_NAME	= '$l_TARGET_FIELD_NAME',
	TRANSFORM_EXPRESSION	= '$l_TRANSFORM_EXPRESSION',
	ARRAY_FIELD_NAME	= '$l_ARRAY_FIELD_NAME'
WHERE
	TRANSFORM_RULE_ID	= '$l_TRANSFORM_RULE_ID'
AND	TRANSFORM_SEQUENCE	= '$l_TRANSFORM_SEQUENCE'
";
#print ">>>>>$sql<<<<\n";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	$sth->finish;
}


sub u_ONLINE_TRAN
{
	local(@i_para) = @_;
print "--- update ONLINE_TRAN($i_para[0],$i_para[1])\n";
#print "       MESSAGE_TYPE:$i_para[0]<\n";
#print "       ONLINE_TRANSACTION_CODE:$i_para[1]<\n";
#print "       DES:$i_para[2]<\n";
#print "       REQ_LAYOUT_ID:$i_para[3]<\n";
#print "       REQ_TRANSFORM_RULE_ID:$i_para[4]<\n";
#print "       RES_LAYOUT_ID:$i_para[5]<\n";
#print "       RES_TRANSFORM_RULE_ID:$i_para[6]<\n";
	$l_MESSAGE_TYPE			= $i_para[0];
	$l_ONLINE_TRANSACTION_CODE	= $i_para[1];
	$l_DESCRIPTION			= $i_para[2];
	$l_REQ_LAYOUT_ID		= $i_para[3];
	$l_REQ_TRANSFORM_RULE_ID	= $i_para[4];
	$l_RES_LAYOUT_ID		= $i_para[5];
	$l_RES_TRANSFORM_RULE_ID	= $i_para[6];
	$l_AUTHOR_ID			= $i_para[7];
	$l_OPERATOR_ID			= $i_para[8]; 
	$l_SOURCE_SYSTEM		= $i_para[9]; 
	$l_TARGET_SYSTEM		= $i_para[10]; 

	$sql_req = "
UPDATE ONLINE_TRAN
SET
	DESCRIPTION		= '$l_DESCRIPTION',
	REQ_LAYOUT_ID		= '$l_REQ_LAYOUT_ID',
	REQ_TRANSFORM_RULE_ID	= '$l_REQ_TRANSFORM_RULE_ID',
	AUTHOR_ID		= '$l_AUTHOR_ID',
	OPERATOR_ID		= '$l_OPERATOR_ID',
	SOURCE_SYSTEM		= '$l_SOURCE_SYSTEM',
	TARGET_SYSTEM		= '$l_TARGET_SYSTEM'
WHERE
	MESSAGE_TYPE		= '$l_MESSAGE_TYPE'
AND	ONLINE_TRANSACTION_CODE	= '$l_ONLINE_TRANSACTION_CODE'
";
	$sql_res = "
UPDATE ONLINE_TRAN
SET
	DESCRIPTION		= '$l_DESCRIPTION',
	RES_LAYOUT_ID		= '$l_RES_LAYOUT_ID',
	RES_TRANSFORM_RULE_ID	= '$l_RES_TRANSFORM_RULE_ID',
	AUTHOR_ID		= '$l_AUTHOR_ID',
	OPERATOR_ID		= '$l_OPERATOR_ID',
	SOURCE_SYSTEM		= '$l_SOURCE_SYSTEM',
	TARGET_SYSTEM		= '$l_TARGET_SYSTEM'
WHERE
	MESSAGE_TYPE		= '$l_MESSAGE_TYPE'
AND	ONLINE_TRANSACTION_CODE	= '$l_ONLINE_TRANSACTION_CODE'
";

#print ">>>>>$sql<<<<\n";
	if( $l_REQ_TRANSFORM_RULE_ID	eq '') {
  		$sth = $db->prepare($sql_res);
	} else {
  		$sth = $db->prepare($sql_req);
	}

	$sth->execute();
  
	$sth->finish;
}

sub i_META_TRANSFORM_LAYOUT
{
	local(@i_para_) = @_;
print "--- insert META_TRANSFORM_LAYOUT($i_para_[0], $i_para_[1])\n";
	$sql = "
INSERT INTO META_TRANSFORM_LAYOUT(
TRANSFORM_RULE_ID,
LAYOUT_ID
) VALUES (
?, ?
)
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para_);
  
	$sth->finish;
}

#----------------------------------------------------------------
sub i_DEFERRED_TRAN_TRANSFORM_RULE
{
	local(@i_para_) = @_;

	$sql = "
INSERT INTO 
DEFERRED_TRAN_TRANSFORM_RULE (
	SOURCE_INTERFACE_ID,
	TARGET_INTERFACE_ID,
	TRANSFORM_RULE_ID,
	AUTHOR_ID,
	OPERATOR_ID
--	LAST_UPDATE_TIME
) VALUES (
?, ?, ?, ?, ?
)
";
  	$sth = $db->prepare($sql);
print "[1]:$i_para_[0]\n";
print "[2]:$i_para_[1]\n";
print "[3]:$i_para_[2]\n";
print "[4]:$i_para_[3]\n";
print "[5]:$i_para_[4]\n";
	$sth->execute(@i_para_);
  
	$sth->finish;
}

#----------------------------------------------------------------
sub i_META_TRANSFORM_EXPRESSION
{
	local(@i_para_) = @_;

print " --- i_META_TRANSFORM_EXPRESSION($i_para_[0],$i_para_[3])\n";
	$sql = "
INSERT INTO META_TRANSFORM_EXPRESSION(
TRANSFORM_RULE_ID,
TRANSFORM_SEQUENCE,
TRANSFORM_TYPE,
TARGET_FIELD_NAME,
TRANSFORM_EXPRESSION,
ARRAY_FIELD_NAME
) VALUES (
?, ?, ?, ?, ?,
?
)
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para_);
  
	$sth->finish;
}

sub i_META_LAYOUT_FIELD
{
	local(@i_para_) = @_;

	$sql = "
INSERT INTO META_LAYOUT_FIELD(
LAYOUT_ID,
FIELD_ID,
FIELD_SEQUENCE,
FIELD_TYPE,
FIELD_LENGTH,
FIELD_PRECISION,
FIELD_ARRAY_SIZE,
FIELD_ATTRIBUTE,
FIELD_ADDITIONAL_INFORMATION 
) VALUES (
?, ?, ?, ?, ?,
?, ?, ?, ?
)
";
  	$sth = $db->prepare($sql);

#$i_para_[2] = 1;
#$i_para_[4] = 1;
#$i_para_[5] = 1;
#$i_para_[6] = 1;

#printf "---- $i_para_[0],";
#printf "$i_para_[1],";
#printf "$i_para_[2],";
#printf "$i_para_[3],";
#printf "$i_para_[4],";
#printf "$i_para_[5],";
#printf "$i_para_[6],";
#printf "$i_para_[7],";
#printf "$i_para_[8]\n";
	$sth->execute(@i_para_);
  
	$sth->finish;
}

sub pr_log
{
	printf "========================\n\n";
}


sub i_ONLINE_TRAN
{
	local(@i_para_) = @_;
print "--- insert ONLINE_TRAN($i_para_[0], $i_para_[1])\n";
	$sql = "
INSERT INTO ONLINE_TRAN(
 MESSAGE_TYPE,
 ONLINE_TRANSACTION_CODE,
 DESCRIPTION ,
 REQ_LAYOUT_ID,
 REQ_TRANSFORM_RULE_ID,
 RES_LAYOUT_ID,
 RES_TRANSFORM_RULE_ID,
 AUTHOR_ID,
 OPERATOR_ID,
SOURCE_SYSTEM,
TARGET_SYSTEM
-- LAST_UPDATE_TIME 
) VALUES (
?, ?, ?, ?, ?,
?, ?, ?, ?, ?,
?
)
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para_);


#print "RC : " . $sth->rows;  
#	$l_cnt = $sth->rows;
#print "00000000000insert rows : $l_cnt\n";
	$sth->finish;

	$i_ONLINE_TRAN = $sth->rows;  
	
}

#----------------------------------------------------------------
sub d_META_LAYOUT_FIELD
{
	local($i_para_) = @_;

	$sql = "
DELETE META_LAYOUT_FIELD
WHERE LAYOUT_ID = ?
";
  	$sth = $db->prepare($sql);

	$sth->execute($i_para_)
		or die "Couldn't execute statement: " . $sth->errstr;

	$sth->finish;
}


sub META_TRANSFORM_RULE
{
	local($app_name, $rule_name, $LAYOUT_ID) = @_;


#	$ii = `./s_rules2.pl $app_name $rule_name`;
#	print "$ii\n";
#	$line = `./s_msg_list3.pl 0 $app_name $rule_name`;
#print "$line\n";
#	chop $line;
#	@val = split ',', $line;
#	$LAYOUT_ID	= $val[6];

#	$s_count = s_table_cnt('META_TRANSFORM_RULE', $rule_name, $LAYOUT_ID);
#	if( $s_count > 0 ) {
#		&d_table('META_TRANSFORM_LAYOUT', $rule_name, $LAYOUT_ID);
#		&d_table('META_TRANSFORM_EXPRESSION', $rule_name, $LAYOUT_ID);
#		&d_table('META_TRANSFORM_RULE', $rule_name, $LAYOUT_ID);
#	}
	$l_TRANSFORM_RULE_ID	= $rule_name;
	$l_LAYOUT_ID		= $LAYOUT_ID;
	$l_DESCRIPTION 		= 'Auto Migration';
	$l_AUTHOR_ID		= 'System';
	$l_OPERATOR_ID		= 'System';
	$l_LAST_UPDATE_TIME	= '';

	@i_para = (
		$l_TRANSFORM_RULE_ID,
		$l_LAYOUT_ID,
		$l_DESCRIPTION,
		$l_AUTHOR_ID,
		$l_OPERATOR_ID
#		$l_LAST_UPDATE_TIME
	);
	$s_count = s_table_cnt('META_TRANSFORM_RULE', $rule_name, $LAYOUT_ID);
	if( $s_count > 0 ) {
		&u_META_TRANSFORM_RULE(@i_para);
	} else {
		&i_META_TRANSFORM_RULE(@i_para);
	}
}

sub META_TRANSFORM_LAYOUT
{
	local($app_name, $rule_name, $LAYOUT_ID) = @_;


#	$line = `./s_msg_list3.pl 0 $app_name $rule_name`;
#print "$line\n";
#	chop $line;
#	@val = split ',', $line;
#	$LAYOUT_ID	= $val[5];

	$s_count = s_table_cnt('META_TRANSFORM_LAYOUT', $rule_name, $LAYOUT_ID);
	if( $s_count > 0 ) {
		&d_table('META_TRANSFORM_LAYOUT', $rule_name, $LAYOUT_ID);
	}

	$l_TRANSFORM_RULE_ID	= $rule_name;
	$l_LAYOUT_ID		= $LAYOUT_ID;

	@i_para = (
		$l_TRANSFORM_RULE_ID,
		$l_LAYOUT_ID
	);

	$s_count = s_table_cnt('META_TRANSFORM_LAYOUT', $rule_name, $LAYOUT_ID);
	&i_META_TRANSFORM_LAYOUT(@i_para);
}

#----------------------------------------------------------------
sub DEFERRED_TRAN_TRANSFORM_RULE
{
	local($app_name, $rule_name) = @_;


#	$ii = 
#	print "$ii\n";
	$line = `./s_rules2.pl $app_name $rule_name`;
#print "$line\n";
	chop $line;
	@val = split ' ', $line;
	$LAYOUT_ID	= $val[4];
print ">>>>$val[3]\n";
print ">>>>$val[7]\n";

	$SOURCE_INTERFACE_ID = $val[3];
	$TARGET_INTERFACE_ID = $val[7];

	$str_len =  length $SOURCE_INTERFACE_ID;
#	print ">> str_len : $str_len\n";

	$SOURCE_INTERFACE_ID = substr $SOURCE_INTERFACE_ID, 1, $str_len - 2;
	$str_len =  length $TARGET_INTERFACE_ID;
	$TARGET_INTERFACE_ID = substr $TARGET_INTERFACE_ID, 1, $str_len - 2;
#	$s_count = s_DEFERRED_TRAN_TRANSFORM_RULE_cnt(
#		$SOURCE_INTERFACE_ID,
#		$TARGET_INTERFACE_ID,
#		$rule_name);
#	if( $s_count > 0 ) {
#		&d_DEFERRED_TRAN_TRANSFORM_RULE(
#			$SOURCE_INTERFACE_ID,
#			$TARGET_INTERFACE_ID,
#			$rule_name);
#	}


	$l_SOURCE_INTERFACE_ID 	= $SOURCE_INTERFACE_ID;
	$l_TARGET_INTERFACE_ID 	= $TARGET_INTERFACE_ID;
	$l_TRANSFORM_RULE_ID	= $rule_name;
	$l_AUTHOR_ID		= 'System';
	$l_OPERATOR_ID		= 'System';
	$l_LAST_UPDATE_TIME	= '';

	@i_para = (
		$l_SOURCE_INTERFACE_ID,
		$l_TARGET_INTERFACE_ID,
		$l_TRANSFORM_RULE_ID,
		$l_AUTHOR_ID,
		$l_OPERATOR_ID
#		$l_LAST_UPDATE_TIME
	);

	&i_DEFERRED_TRAN_TRANSFORM_RULE(@i_para);
}

sub META_TRANSFORM_EXPRESSION
{
	local($app_name, $rule_name, $LAYOUT_ID) = @_;

#	$line = `./s_msg_list3.pl 0 $app_name $rule_name`;
#print "$line\n";
#	chop $line;
#	@val = split ',', $line;
#	$LAYOUT_ID	= $val[6];
#	$SRC_LAYOUT	= $val[5];
#print "META_TRANSFORMATION_EXPRESSION.LAYOUT_ID:$LAYOUT_ID\n";

	open(FILE, "<data/$LAYOUT_ID.fmt");
	$i = 0;
	$m_size = 0;
	while( $line = <FILE> ) {
		chop $line;
		@val = split ',', $line;

#print "META_TRANSFORMATION_EXPRESSION.loop.: $LAYOUT_ID.$val[2], $val[4]\n";

		$l_TRANSFORM_RULE_ID 	= $rule_name;
		$l_TRANSFORM_SEQUENCE 	= $val[1];
		$l_TRANSFORM_TYPE 	= 'C';
		$l_TARGET_FIELD_NAME 	= $LAYOUT_ID.'.'.$val[2];
		$l_TRANSFORM_EXPRESSION = $SRC_LAYOUT.'.'.$val[4];
		$l_ARRAY_FIELD_NAME 	= '';

#print ">>>>$val[4]<<<<\n";
		if( $val[3] eq 'OC.DFLT.H') {
			$l_TRANSFORM_TYPE 	= 'E';
			$l_TRANSFORM_EXPRESSION = '"H"';
		} elsif( $val[4] eq 'NONE' ) {
			next;
		} 

		@i_para = (
			$l_TRANSFORM_RULE_ID,
			$l_TRANSFORM_SEQUENCE,
			$l_TRANSFORM_TYPE,
			$l_TARGET_FIELD_NAME,
			$l_TRANSFORM_EXPRESSION,
			$l_ARRAY_FIELD_NAME
		);

		$s_count = s_META_TRANSFORM_EXPRESSION_cnt(
			$l_TRANSFORM_RULE_ID,
			$l_TRANSFORM_SEQUENCE
			);
		if( $s_count > 0 ) {
			&u_META_TRANSFORM_EXPRESSION(@i_para);
		} else {
			&i_META_TRANSFORM_EXPRESSION(@i_para);
		}
		$i += 1;
	}
}

sub META_LAYOUT_FIELD
{
	local($fmt_name) = @_;
#print "$fmt_name\n";

#	$s_count = s_META_LAYOUT_FIELD_cnt($fmt_name);
#	if( $s_count > 0 ) {
#		&d_META_LAYOUT_FIELD($fmt_name);
#	}

	open(FILE, "<data/$fmt_name.fmt");
	$i = 0;
	$m_size = 0;
	while( $line = <FILE> ) {
		chop $line;
		@val = split ',', $line;
#		printf "[0]:$val[0] ";
#		printf "[1]:$val[1] ";
#		printf "[2]:$val[2]\n";

		$l_LAYOUT_ID		= $val[0];
		$l_FIELD_ID 		= $val[2];
		$l_FIELD_SEQUENCE 	= $val[1];
		$l_FIELD_TYPE 		= 'x';
		$l_FIELD_LENGTH 	= $val[6];
		$l_FIELD_PRECISION 	= 0;
		$l_FIELD_ARRAY_SIZE 	= 0;
		$l_FIELD_ATTRIBUTE 	= ' ';
		$l_FIELD_ADDITIONAL_INFORMATION = '';

$m_size += $l_FIELD_LENGTH;
#print "--$l_FIELD_LENGTH\n";
		@i_para = (
			$l_LAYOUT_ID		,
			$l_FIELD_ID 		,
			$l_FIELD_SEQUENCE 	,
			$l_FIELD_TYPE 		,
			$l_FIELD_LENGTH 	,
			$l_FIELD_PRECISION 	,
			$l_FIELD_ARRAY_SIZE 	,
			$l_FIELD_ATTRIBUTE 	,
			$l_FIELD_ADDITIONAL_INFORMATION 
		);
		&i_META_LAYOUT_FIELD(@i_para);

		$i += 1;
	}
#print "--msg_size : $m_size\n";
}
sub get_MESSAGE_TYPE
{
	local($FLD_NAME1) = @_;

	if ( $FLD_NAME1 eq 'STR0001' ) {
		$l_MESSAGE_TYPE = 'I';
	} elsif ( $FLD_NAME1 eq 'STR0015' ) {
		$l_MESSAGE_TYPE = 'L';
	} else {
		$l_MESSAGE_TYPE = 'E';
	}
}
sub ONLINE_TRAN
{
	local(	$FLD_NAME1, 
		$ARG_VAL, 
		$l_REQ, 
		$app_name, 
		$rule_name,
		$l_LAYOUT_ID
		) = @_;

# 2010.01.27
	$l_LAYOUT_ID		=~ tr/-/_/;

#	if ( $FLD_NAME1 eq 'STR0001' ) {
#		$l_MESSAGE_TYPE = 'I';
#	} elsif ( $FLD_NAME1 eq 'STR0001' ) {
#		$l_MESSAGE_TYPE = 'L';
#	} else {
#		$l_MESSAGE_TYPE = 'E';
#	}
	$l_MESSAGE_TYPE = &get_MESSAGE_TYPE($FLD_NAME1);

#printf "--- ONLINE_TRAN.MESSAGE_TYPE:$l_MESSAGE_TYPE<\n";
#printf "--- ONLINE_TRAN.rule_name:$rule_name<\n";
	$l_ONLINE_TRANSACTION_CODE = $ARG_VAL;
	$l_DESCRIPTION 		= 'Auto Migration';
	$l_REQ_LAYOUT_ID 	= '';
	$l_REQ_TRANSFORM_RULE_ID= '';
	$l_RES_LAYOUT_ID	= '';
	$l_RES_TRANSFORM_RULE_ID= '';
	$l_AUTHOR_ID		= 'System';
	$l_OPERATOR_ID		= 'System';
	$l_SOURCE_SYSTEM	= ' ';
	$l_TARGET_SYSTEM	= ' ';

	if( $l_REQ eq 'Y' ) {
		$l_REQ_LAYOUT_ID 	= $l_LAYOUT_ID;
		$l_REQ_TRANSFORM_RULE_ID= $rule_name;
	} else {
		$l_RES_LAYOUT_ID	= $l_LAYOUT_ID;
		$l_RES_TRANSFORM_RULE_ID= $rule_name;
	}
#printf "--- ONLINE_TRAN.l_RES_LAYOUT_ID:$l_RES_LAYOUT_ID<\n";
#printf "--- ONLINE_TRAN.l_RES_TRANSFORM_RULE_ID:$l_RES_TRANSFORM_RULE_ID<\n";

	@i_para = (
		$l_MESSAGE_TYPE,
		$l_ONLINE_TRANSACTION_CODE,
		$l_DESCRIPTION,
		$l_REQ_LAYOUT_ID,
		$l_REQ_TRANSFORM_RULE_ID,
		$l_RES_LAYOUT_ID,
		$l_RES_TRANSFORM_RULE_ID,
		$l_AUTHOR_ID,
		$l_OPERATOR_ID,
		$l_SOURCE_SYSTEM,
		$l_TARGET_SYSTEM
	);

	$l_cnt = &s_ONLINE_TRAN_cnt(
		$l_MESSAGE_TYPE,
		$l_ONLINE_TRANSACTION_CODE
	);
#print " s_ONLINE_TRAN_cnt.cnt : $l_cnt\n";
	if ($l_cnt > 0) {
		$l_err = &u_ONLINE_TRAN(@i_para);
	} else {
		$l_err = &i_ONLINE_TRAN(@i_para);
	}
#printf "i_ONLINE_TRAN l_err :$l_err\n",

}

sub ONLINE_TRAN_
{
	local($app_name, $rule_name) = @_;

	$is_in_out = &s_is_in_or_out_tran($app_name, $rule_name);

print "--- is_in_out : $is_in_out\n";
	if( ($is_in_out eq 'L') || ($is_in_out eq 'I') ) {
		$is_normal = 1;		
		$l_MESSAGE_TYPE = $is_in_out;

	} elsif($is_in_out eq 'L1') {
		$is_normal = 0;		
		$l_MESSAGE_TYPE = 'L';

	} elsif($is_in_out eq 'I1') {
		$is_normal = 0;		
		$l_MESSAGE_TYPE = 'I';

	} else {
		$is_normal = 0;		
		$l_MESSAGE_TYPE = 'E';

	}


print "--- return : $l_MESSAGE_TYPE\n";
	$l_ONLINE_TRANSACTION_CODE = &s_online_tran_code($app_name, $rule_name);
print "--- return ONLINE_TRANSACTION_CODE : $l_ONLINE_TRANSACTION_CODE\n";
	$l_DESCRIPTION 		= 'Auto Migration';
	$l_REQ_LAYOUT_ID 	= '';
	$l_REQ_TRANSFORM_RULE_ID	= '';
	$l_RES_LAYOUT_ID	= '';
	$l_RES_TRANSFORM_RULE_ID	= '';
	$l_AUTHOR_ID		= 'System';
	$l_OPERATOR_ID		= 'System';

	$l_cnt = &s_ONLINE_TRAN_cnt(
		$l_MESSAGE_TYPE,
		$l_ONLINE_TRANSACTION_CODE
	);
#print " s_ONLINE_TRAN_cnt.cnt : $l_cnt\n";
	if ($l_cnt > 0) {
		&d_ONLINE_TRAN_TRANSFORM_RULE(
			$l_MESSAGE_TYPE,
			$l_ONLINE_TRANSACTION_CODE
		);
		&d_ONLINE_TRAN(
			$l_MESSAGE_TYPE,
			$l_ONLINE_TRANSACTION_CODE
		);
	}

	@i_para = (
		$l_MESSAGE_TYPE,
		$l_ONLINE_TRANSACTION_CODE,
		$l_DESCRIPTION,
		$l_REQ_LAYOUT_ID,
		$l_REQ_TRANSFORM_RULE_ID,
		$l_RES_LAYOUT_ID,
		$l_RES_TRANSFORM_RULE_ID,
		$l_AUTHOR_ID,
		$l_OPERATOR_ID
	);
	$l_err = &i_ONLINE_TRAN(@i_para);
#printf "i_ONLINE_TRAN l_err :$l_err\n",
#	$ONLINE_TRAN = -1;
#printf "i_ONLINE_TRAN l_err :$l_err\n",
}

sub s_COMPARE_SEQUENCE
{
	local(
		$l_MESSAGE_TYPE,
		$l_ONLINE_TRANSACTION_CODE,
		$l_REQ_RES_YN
			) = @_;

#print "--- s_ONLINE_TRAN(): $i_para[0], $i_para[1], $i_para[2],";
#print "$i_para[3], $i_para[4], $i_para[5]\n";

	$sql = "
SELECT 
COUNT(*) + 1
FROM 	ONLINE_TRAN_TRANSFORM_RULE
WHERE 
	MESSAGE_TYPE		= '$l_MESSAGE_TYPE'
AND	ONLINE_TRANSACTION_CODE	= '$l_ONLINE_TRANSACTION_CODE'
AND	REQ_RES_YN		= '$l_REQ_RES_YN'
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	@data = $sth -> fetchrow_array;

	$sth->finish;
#print "--- s_COMPARE_SEQUENCE():$data[0]\n";
	$s_COMPARE_SEQUENCE = $data[0];
}

sub ONLINE_TRAN_TRANSFORM_RULE
{
	local($ARG_VAL, 
		$REQ,
		$app_name, $rule_name, $fmt_name,
		$COMPARE_EXPRESSION
		) = @_;


	$l_MESSAGE_TYPE 		= &get_MESSAGE_TYPE($FLD_NAME1);

#printf "--- ONLINE_TRAN_TRANSFORM_RULE.l_MESSAGE_TYPE :$l_MESSAGE_TYPE\n";
#printf "--- ONLINE_TRAN_TRANSFORM_RULE.REQ :$REQ<\n";
	$l_ONLINE_TRANSACTION_CODE 	= $ARG_VAL;
	$l_REQ_RES_YN			= $REQ;

	$l_COMPARE_SEQUENCE		= &s_COMPARE_SEQUENCE(
					$l_MESSAGE_TYPE,
					$l_ONLINE_TRANSACTION_CODE,
					$l_REQ_RES_YN);

#printf "--- ONLINE_TRAN_TRANSFORM_RULE.COMPARE_SEQUENCE :$l_COMPARE_SEQUENCE<\n";

	$l_COMPARE_EXPRESSION		=`07_COMPARE_EXPRESSION.pl $app_name $rule_name $REQ`;

	
#	$str_len = length $l_COMPARE_EXPRESSION;
#	$tmp_str = substr $l_COMPARE_EXPRESSION	, $str_len-5,2;

#printf "-------\n";
#printf "------- >$tmp_str< \n";
#printf "-------\n";
#	if( ($tmp_str eq '&&') ||
#		($tmp_str eq '||') ){
#		$l_COMPARE_EXPRESSION = substr $l_COMPARE_EXPRESSION, 0, $str_len - 5;
#	}
	$l_COMPARE_EXPRESSION =~ s/STR/CF_A2A01_I01.STR/g;
	$l_COMPARE_EXPRESSION =~ s/^ //g;
#printf "-------------------- >$l_COMPARE_EXPRESSION<\n";
	chomp $l_COMPARE_EXPRESSION;
	$l_COMPARE_EXPRESSION =~ s/\r//g;
	$l_COMPARE_EXPRESSION =~ s/ $//g;
	$l_COMPARE_EXPRESSION =~ s/^&& //g;
	$l_COMPARE_EXPRESSION =~ s/ &&$//g;
	$l_COMPARE_EXPRESSION =~ s/&& &&/&&/g;
#printf "-------------------- >$l_COMPARE_EXPRESSION<\n";

####################### KCB
	if ($l_COMPARE_EXPRESSION eq '') {
		$l_COMPARE_EXPRESSION = '0';
	}
#######################
printf "--- ONLINE_TRAN_TRANSFORM_RULE.l_COMPARE_EXPRESSION :$l_COMPARE_EXPRESSION<,$app_name,$rule_name\n";

	$l_TRANSFORM_RULE_ID		= $rule_name;
#printf "--- ONLINE_TRAN_TRANSFORM_RULE.l_TRANSFORM_RULE_ID :$l_TRANSFORM_RULE_ID<\n";

	@i_para = (
		$l_MESSAGE_TYPE,
		$l_ONLINE_TRANSACTION_CODE,
		$l_REQ_RES_YN,
		$l_COMPARE_SEQUENCE,
		$l_COMPARE_EXPRESSION,
		$l_TRANSFORM_RULE_ID
	);
	&i_ONLINE_TRAN_TRANSFORM_RULE(@i_para);
}

$FLD_NAME1 	= @ARGV[0];
$ARG_VAL 	= @ARGV[1];
$REQ 		= @ARGV[2];
$app_name 	= @ARGV[3];
$rule_name 	= @ARGV[4];
$fmt_name 	= @ARGV[5];
$EXP 	= @ARGV[6];

#$tt=`ls`;
#print "$tt\n";

open(FILE, ">>data/$app_name.err");

print "6. migraion online tran : $app_name, $rule_name, $fmt_name\n";
$db = DBI->connect('DBI:ODBC:'.$TGT_DB, 'hubfmt', 'hubfmt');
$db2 = DBI->connect('DBI:ODBC:'.$SRC_DB, 'hubiio', 'hubiio');

	if( ($rule_name eq '0') ) {
		$rule_name = '';
	}
#print " FLD_NAME1:$FLD_NAME1,ARG_VAL:$ARG_VAL<\n";
#print " REQ:$REQ,app_name:$app_name<\n";
#print " rule_name:$rule_name,fmt_name:$fmt_name<\n";
	if( ($fmt_name eq '') ) {
		$rule_name = '';
	}
	if( $EXP eq '' ) {

		$ret1 = &ONLINE_TRAN(
			$FLD_NAME1, 
			$ARG_VAL,
			$REQ,
			$app_name, 
			$rule_name, 
			$fmt_name);
	}


	if( $EXP eq 'todo' ) {

		$l_MESSAGE_TYPE = &get_MESSAGE_TYPE($FLD_NAME1);
		$l_cnt = &s_ONLINE_TRAN_cnt(
			$l_MESSAGE_TYPE,
			$ARG_VAL
		);

		if ($l_cnt < 1) {
			$l_ONLINE_TRANSACTION_CODE = $ARG_VAL;
			$l_DESCRIPTION 		= 'Auto Migration';
			$l_REQ_LAYOUT_ID 	= '';
			$l_REQ_TRANSFORM_RULE_ID= '';
			$l_RES_LAYOUT_ID	= '';
			$l_RES_TRANSFORM_RULE_ID= '';
			$l_AUTHOR_ID		= 'System';
			$l_OPERATOR_ID		= 'System';
			$l_SOURCE_SYSTEM	= ' ';
			$l_TARGET_SYSTEM	= ' ';

			@i_para = (
				$l_MESSAGE_TYPE,
				$l_ONLINE_TRANSACTION_CODE,
				$l_DESCRIPTION,
				$l_REQ_LAYOUT_ID,
				$l_REQ_TRANSFORM_RULE_ID,
				$l_RES_LAYOUT_ID,
				$l_RES_TRANSFORM_RULE_ID,
				$l_AUTHOR_ID,
				$l_OPERATOR_ID,
				$l_SOURCE_SYSTEM,
				$l_TARGET_SYSTEM
			);
			$l_err = &i_ONLINE_TRAN(@i_para);
		}

		if( ($rule_name ne '') ) {
#print "rule_name is not null\n";
			&ONLINE_TRAN_TRANSFORM_RULE(
				$ARG_VAL,
				$REQ,
				$app_name, $rule_name, $fmt_name,
				$COMPARE_EXPRESSION);
		}
	}


#	&META_TRANSFORM_RULE($app_name, $rule_name, $fmt_name);
#	&META_TRANSFORM_EXPRESSION($app_name, $rule_name, $fmt_name);
#	&META_TRANSFORM_LAYOUT($app_name, $rule_name, $fmt_name);
#if( $app_name eq 'S_A2A003') {
#	&META_TRANSFORM_RULE($app_name, $rule_name);
#	&META_TRANSFORM_EXPRESSION($app_name, $rule_name);
#	&META_TRANSFORM_LAYOUT($app_name, $rule_name);
#	&DEFERRED_TRAN_TRANSFORM_RULE($app_name, $rule_name);
#} elsif( ($app_name eq 'S_A2A001') ||
#	($app_name eq 'S_A2A002') ||
#	($app_name eq 'S_A2A004') ||
#	($app_name eq 'S_A2A005') ) {
#		&META_TRANSFORM_RULE($app_name, $rule_name);
#} else {
#		&META_TRANSFORM_LAYOUT($app_name, $rule_name);
#		&META_TRANSFORM_RULE($app_name, $rule_name);
#		&META_TRANSFORM_EXPRESSION($app_name, $rule_name);
#}

$db->disconnect;
$db2->disconnect;

if( $ret1 == -1 )  {
print "----------- file write\n";
	print FILE "$app_name,$rule_name\n";
}



